﻿using Dapper;
using System.Data.SqlClient;
using System.Reflection.Emit;
using yunbo.Model.BIP;

namespace yunbo.Repositorys.FPMRepositorys
{
    public class BD_CPSXRepository
    {
        public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User ID=sa;Password=geili@2025;";

        public List<BD_CPSXDY> Getcpsxname(string cp_code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select cpsxdy.sx_code ,cpsxdy.sx_name ,cpsxdy.sx_zhi from [dbo].[bd_cpsx] as cpsx INNER JOIN [dbo].[bd_cpsxdy] as cpsxdy ON cpsx.sx_code= cpsxdy.sx_code where  cp_code='{cp_code}'";
                List<BD_CPSXDY> bd_cp = connection.Query<BD_CPSXDY>(sql).ToList(); // 使用 ToList() 获取所有菜单  
                return bd_cp;
            }
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="cPTP"></param>
        /// <returns></returns>
        public int AddCPTP(BD_CPTP cPTP)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "insert into bd_cptp(cptp_code,cptp_name,cptp_zhi,cp_code)values(@code,@name,@zhi,@cp)";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并绑定到 BD_CP 对象的属性
                    command.Parameters.AddWithValue("@code", cPTP.cptp_code);
                    command.Parameters.AddWithValue("@name", cPTP.cptp_name);
                    command.Parameters.AddWithValue("@zhi", cPTP.cptp_zhi);
                    command.Parameters.AddWithValue("@cp", cPTP.cp_code);

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="cPTP"></param>
        /// <returns></returns>
        public int UpdateCPTP(BD_CPTP cPTP)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "UPDATE bd_cptp SET cptp_name = @name,cptp_zhi = @zhi,cp_code = @cp WHERE cptp_code = @code;  ";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并绑定到 BD_CP 对象的属性
                    command.Parameters.AddWithValue("@code", cPTP.cptp_code);
                    command.Parameters.AddWithValue("@name", cPTP.cptp_name);
                    command.Parameters.AddWithValue("@zhi", cPTP.cptp_zhi);
                    command.Parameters.AddWithValue("@cp", cPTP.cp_code);

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="cPTP"></param>
        /// <returns></returns>
        public int DeleteCPTP(string code)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = "delete from bd_cptp where cptp_code = @code";
                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    // 添加参数并绑定到 BD_CP 对象的属性
                    command.Parameters.AddWithValue("@code", code);

                    // 打开连接并执行命令
                    connection.Open();
                    int affectedRows = command.ExecuteNonQuery();

                    // 返回受影响的行数
                    return affectedRows;
                }
            }
        }
        public List<BD_CPSXDY> GetBD_CPSX(string name)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select * from [dbo].[bd_cpsxdy] where sx_name='{name}'";
                List<BD_CPSXDY> bD_CPSXDies = connection.Query<BD_CPSXDY>(sql).ToList(); // 使用 ToList() 获取所有菜单  
                return bD_CPSXDies;
            }
        }
        public List<BD_CPSXDY> GetBD_CPSXFZ()
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"select sx_name from [dbo].[bd_cpsxdy] group by sx_name";
                List<BD_CPSXDY> bD_CPSXDies = connection.Query<BD_CPSXDY>(sql).ToList(); // 使用 ToList() 获取所有菜单  
                return bD_CPSXDies;
            }
        }
        public int PostBD_CPSX(BD_CPSX bD_CPSX)
        {
            using (SqlConnection connection = new SqlConnection(data))
            {
                string sql = $"insert into [dbo].[bd_cpsx](sx_code,cp_code) values('{bD_CPSX.cpsx_code}','{bD_CPSX.cp_code}')";
                int rs = connection.Execute(sql);
                return rs;
            }
        }
    }
}
